package com.cx.healthex.wxsmall.dao;

import java.io.File;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.cx.healthex.base.apiModel.PageModel;
import com.cx.healthex.base.dao.BaseDAO;

/**
 * xml配置sql查询
 * cenxi
 * 2017-10-17
 * */
@Repository
public class GeneralSqlDAO extends BaseDAO{
	
	static String parentName = "opt";
	static URL url = null;
	static String path = null;	
	static Document doc = null;
	static String resource = "admin_sql.xml";
	
	static {
		url = GeneralSqlDAO.class.getClassLoader().getResource(resource);
		try {
			path = URLDecoder.decode(url.getPath(), "UTF-8");
			SAXReader reader = new SAXReader();
			doc = reader.read(new File(path));
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (DocumentException e) {
			e.printStackTrace();
		}
	}
	
	public void refreshSql(){
		url = GeneralSqlDAO.class.getClassLoader().getResource(resource);
		try {
			path = URLDecoder.decode(url.getPath(), "UTF-8");
			SAXReader reader = new SAXReader();
			doc = reader.read(new File(path));
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (DocumentException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 根据编号查询sql
	 * */
	public String findSqlByNo(String sqlNo){
		String sql = "";
		Element root = null;
		if (doc != null) {
			root = doc.getRootElement();
		}
		Element sqlNode = null;
		if (root != null) {
			sqlNode = root.element(parentName);
		}
		if (sqlNode != null) {
			sql = sqlNode.elementTextTrim(sqlNo);
		}
		return sql;
	}
	
	/**
	 * 完成sql 与 传参
	 * */
	public HashMap<String, Object> finishSqlAndparameter(String serialNumber,HashMap<String, Object> paramMap){
		HashMap<String, Object> result = new HashMap<String, Object>();
		List<Object> param = new ArrayList<Object>();
		//获取sql
		String sql = findSqlByNo(serialNumber);
		
		// 预处理各种标签。by huangym at 2016/12/29 start
		sql = ifnull(sql, paramMap);
		sql = ifnotnull(sql, paramMap);
		sql = ifempty(sql, paramMap);
		sql = ifnotempty(sql, paramMap);
		// 预处理各种标签。by huangym at 2016/12/29 end
		
		//获取表头
		String sqlHead = findSqlByNo(serialNumber+"_head");
		if(sql != null && !"".equals(sql)){
			
			//设置分表
			if(paramMap.get("actId") != null){
				sql = sql.replaceAll("#tableActId#", paramMap.get("actId").toString());
			}
			
			while(sql.indexOf("#") > 0){
				String name = sql.substring(sql.indexOf("#")+1, sql.indexOf("#",sql.indexOf("#")+1));
				String condition = sql.substring(sql.indexOf("["), sql.indexOf("]")+1);
				Object obj = paramMap.get(name);
				if(obj != null && obj.toString() != null && !"".equals(obj.toString()) && !"".equals(obj+"") && !"null".equals(obj.toString()) && !"null".equals(obj+"")&& !"undefined".equals(obj.toString()) && !"undefined".equals(obj+"")){
					sql = sql.replaceFirst("#"+name+"#", "?");
					
					if(condition.indexOf(" like ") > 0  || condition.indexOf(" LIKE ") > 0){
						param.add("%"+paramMap.get(name).toString()+"%");
					}else{
						param.add(paramMap.get(name));
					}
					sql = sql.replaceFirst("\\[", " ");
					sql = sql.replaceFirst("\\]", " ");
				}else{
					String condition2 = sql.substring(sql.indexOf("["), sql.indexOf("]")+1);
					sql = sql.replace(condition2, " ");
				}
			}
			sql = sql.replace("[", " ");
			sql = sql.replace("]", " ");
			
			result.put("sql", sql);
			result.put("sqlHead", sqlHead);
			result.put("parameter", param);
		}
		
		//System.out.println(sql);
		//System.out.println(param.toArray());
		
		return result;
		
	}
	
	/**
	 * 处理ifnull标签
	 * @param sql
	 * @return
	 */
	private String ifnull(String sql, HashMap<String, Object> paramMap) {
		String PREFIX = "[ifnull ";
		String PREFIX_END = "]";
		String SUFFIX = "[/ifnull]";
		String PROPERTY_PREFIX = "name=\"#";
		String PROPERTY_SUFFIX = "#";
		StringBuilder sb = new StringBuilder();
		while (sql.indexOf(PREFIX) >= 0) {
			sb.append(sql.substring(0, sql.indexOf(PREFIX)));
			
			String match = sql.substring(sql.indexOf(PREFIX) + PREFIX.length(), sql.indexOf(SUFFIX));
			// 规范写法：[ifnull name="#cpiId#"]select * from[/ifnull]
			String name = match.substring(match.indexOf(PROPERTY_PREFIX) + PROPERTY_PREFIX.length(), 
					match.indexOf(PROPERTY_SUFFIX, match.indexOf(PROPERTY_PREFIX) + PROPERTY_PREFIX.length()));
			Object obj = paramMap.get(name);
			if (obj == null) {
				String str = match.substring(match.indexOf(PREFIX_END) + 1);
				sb.append(str);
			}
			
			sql = sql.substring(sql.indexOf(SUFFIX) + SUFFIX.length());
		}
		sb.append(sql);
		return sb.toString();
	}
	
	/**
	 * 处理ifnotnull标签
	 * @param sql
	 * @return
	 */
	private String ifnotnull(String sql, HashMap<String, Object> paramMap) {
		String PREFIX = "[ifnotnull ";
		String PREFIX_END = "]";
		String SUFFIX = "[/ifnotnull]";
		String PROPERTY_PREFIX = "name=\"#";
		String PROPERTY_SUFFIX = "#";
		StringBuilder sb = new StringBuilder();
		while (sql.indexOf(PREFIX) >= 0) {
			sb.append(sql.substring(0, sql.indexOf(PREFIX)));
			
			String match = sql.substring(sql.indexOf(PREFIX) + PREFIX.length(), sql.indexOf(SUFFIX));
			// 规范写法：[ifnotnull name="#cpiId#"]select * from[/ifnotnull]
			String name = match.substring(match.indexOf(PROPERTY_PREFIX) + PROPERTY_PREFIX.length(), 
					match.indexOf(PROPERTY_SUFFIX, match.indexOf(PROPERTY_PREFIX) + PROPERTY_PREFIX.length()));
			Object obj = paramMap.get(name);
			if (obj != null) {
				String str = match.substring(match.indexOf(PREFIX_END) + 1);
				sb.append(str);
			}
			
			sql = sql.substring(sql.indexOf(SUFFIX) + SUFFIX.length());
		}
		sb.append(sql);
		return sb.toString();
	}
	
	/**
	 * 处理ifempty标签，一般用于字符串
	 * @param sql
	 * @return
	 */
	private String ifempty(String sql, HashMap<String, Object> paramMap) {
		String PREFIX = "[ifempty ";
		String PREFIX_END = "]";
		String SUFFIX = "[/ifempty]";
		String PROPERTY_PREFIX = "name=\"#";
		String PROPERTY_SUFFIX = "#";
		StringBuilder sb = new StringBuilder();
		while (sql.indexOf(PREFIX) >= 0) {
			sb.append(sql.substring(0, sql.indexOf(PREFIX)));
			
			String match = sql.substring(sql.indexOf(PREFIX) + PREFIX.length(), sql.indexOf(SUFFIX));
			// 规范写法：[ifempty name="#cpiId#"]select * from[/ifempty]
			String name = match.substring(match.indexOf(PROPERTY_PREFIX) + PROPERTY_PREFIX.length(), 
					match.indexOf(PROPERTY_SUFFIX, match.indexOf(PROPERTY_PREFIX) + PROPERTY_PREFIX.length()));
			Object obj = paramMap.get(name);
			if (obj == null || "".equals(((String) obj).trim())) {
				String str = match.substring(match.indexOf(PREFIX_END) + 1);
				sb.append(str);
			}
			
			sql = sql.substring(sql.indexOf(SUFFIX) + SUFFIX.length());
		}
		sb.append(sql);
		return sb.toString();
	}
	
	/**
	 * 处理ifnotempty标签，一般用于字符串
	 * @param sql
	 * @return
	 */
	private String ifnotempty(String sql, HashMap<String, Object> paramMap) {
		String PREFIX = "[ifnotempty ";
		String PREFIX_END = "]";
		String SUFFIX = "[/ifnotempty]";
		String PROPERTY_PREFIX = "name=\"#";
		String PROPERTY_SUFFIX = "#";
		StringBuilder sb = new StringBuilder();
		while (sql.indexOf(PREFIX) >= 0) {
			sb.append(sql.substring(0, sql.indexOf(PREFIX)));
			
			String match = sql.substring(sql.indexOf(PREFIX) + PREFIX.length(), sql.indexOf(SUFFIX));
			// 规范写法：[ifnotempty name="#cpiId#"]select * from[/ifnotempty]
			String name = match.substring(match.indexOf(PROPERTY_PREFIX) + PROPERTY_PREFIX.length(), 
					match.indexOf(PROPERTY_SUFFIX, match.indexOf(PROPERTY_PREFIX) + PROPERTY_PREFIX.length()));
			Object obj = paramMap.get(name);
			if (obj != null && !"".equals(((String) obj).trim())) {
				String str = match.substring(match.indexOf(PREFIX_END) + 1);
				sb.append(str);
			}
			
			sql = sql.substring(sql.indexOf(SUFFIX) + SUFFIX.length());
		}
		sb.append(sql);
		return sb.toString();
	}
	
	
	/**
	 * 分页查询返回指定对象
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public PageModel queryObjPageModel(HashMap<String, Object> paraMap,String serialNumber,Integer pageNum, Integer itemCount,Class clazz) throws Exception{
		//获取sql和传参
		HashMap<String, Object> sqlAndparameterMap = finishSqlAndparameter(serialNumber, paraMap);
		if(sqlAndparameterMap != null){
			String sql = (String)sqlAndparameterMap.get("sql");
			List<Object> parameter = (List<Object>)sqlAndparameterMap.get("parameter");
			return getPageList(pageNum, itemCount, sql, parameter.toArray(), ParameterizedBeanPropertyRowMapper.newInstance(clazz));
		}
		return null;
	}
	
	/**
	 * 查询所有返回map对象
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryMapAll(HashMap<String, Object> paraMap,String serialNumber) throws Exception{
		//获取sql和传参
		HashMap<String, Object> sqlAndparameterMap = finishSqlAndparameter(serialNumber, paraMap);
		if(sqlAndparameterMap != null){
			String sql = (String)sqlAndparameterMap.get("sql");
			List<Object> parameter = (List<Object>)sqlAndparameterMap.get("parameter");
			
			List<Map<String, Object>> list = null;
			try {
				list = jdbcTemplate.queryForList(sql, parameter.toArray());
			} catch (Exception e) {
				e.printStackTrace();
			}
			return list;
		}
		return null;
	}
}
